*-------------------------------------------------------------------------------------------------------*
*RESEARCHERS:		Mette Foged, Linea Hasager and Vasil Yasenov
*PROJECT:			Meta-analysis
*DESCRIPTION:		1  Create table showing variation in institutions by country and number of estimates/papers
*-------------------------------------------------------------------------------------------------------*

*ssc install listtex
**************************************************************************************************
* TABLE : #estimates and # papers by countries. Mean and sd for institutions by country *
**************************************************************************************************

*************************
*Number of papers (wage)*
*************************
clear all
set more off


use "${data}\database_clean.dta", replace


replace empl=. if empl==0
replace wage=. if wage==0


drop region
gen region="Nordic countries" if country == "Norway" | country == "Sweden" |country == "Denmark" |country == "Finland"

replace region="Rest of Europe" if country=="Austria" | country=="France" | country=="Germany" | country=="Greece" ///
	| country=="Ireland" | country=="Italy" | country=="Netherlands" | country=="Portugal" | country=="Spain" ///
	| country=="Switzerland" | country=="United Kingdom" | country=="Western Europe" | country=="European Economic Area" | country=="Albania" ///
	| country == "Norway" | country == "Sweden" |country == "Denmark" |country == "Finland"

replace region="North America"	if country=="Canada" | country=="United States" | country=="United States, Canada" 

replace region="Rest of the world" if country=="OECD countries" | country=="Australia" | country=="Israel" | country=="Turkey" 

	*Total
	preserve
	keep paper_id
	duplicates drop
	egen n_p_wage=count(paper_id)
	drop paper_id
	duplicates drop
	sa "$data/tot.dta", replace
	restore

*By countries	
preserve
egen paper_c = group(paper_id country)
order paper_c

duplicates drop paper_c, force
collapse (count) n_p_wage=paper_c, by(country)
label variable n_p_wage "Papers (wage)"
save "$data/n_countries.dta", replace
restore	

*By regions	
egen paper_c = group(paper_id region)
order paper_c
duplicates drop paper_c, force
collapse (count) n_p_wage=paper_c, by(region)
rename region country

	append using "$data/n_countries.dta"
	append using "$data/tot.dta"
	label variable n_p_wage "Papers (wage)"
	
	save "${data}/n_p_wage.dta", replace



*******************************
*Number of papers (employment)*
*******************************
clear all
set more off

use "${data}\database_clean.dta", replace


replace empl=. if empl==0
replace wage=. if wage==0

keep if empl==1

drop region
gen region="Nordic countries" if country == "Norway" | country == "Sweden" |country == "Denmark" |country == "Finland"

replace region="Rest of Europe" if country=="Austria" | country=="France" | country=="Germany" | country=="Greece" ///
	| country=="Ireland" | country=="Italy" | country=="Netherlands" | country=="Portugal" | country=="Spain" ///
	| country=="Switzerland" | country=="United Kingdom" | country=="Western Europe" | country=="European Economic Area" | country=="Albania" ///
	| country == "Norway" | country == "Sweden" |country == "Denmark" |country == "Finland"

replace region="North America"	if country=="Canada" | country=="United States" | country=="United States, Canada" 

replace region="Rest of the world" if country=="OECD countries" | country=="Australia" | country=="Israel" | country=="Turkey" 

	*Total
	preserve
	keep paper_id
	duplicates drop
	egen n_p_empl=count(paper_id)
	drop paper_id
	duplicates drop
	sa "$data/tot.dta", replace
	restore

*By countries	
preserve
egen paper_c = group(paper_id country)
order paper_c

duplicates drop paper_c, force
collapse (count) n_p_empl=paper_c, by(country)
label variable n_p_empl "Papers (employment)"
save "$data/n_countries.dta", replace
restore	

*By regions	
egen paper_c = group(paper_id region)
order paper_c
duplicates drop paper_c, force
collapse (count) n_p_empl=paper_c, by(region)
rename region country

	append using "$data/n_countries.dta"
	append using "$data/tot.dta"
	label variable n_p_empl "Papers (employment)"
	
save "${data}/n_p_empl.dta", replace


****************************
*Number of estimates (wage)*
****************************
clear all
set more off


use "${data}\database_clean.dta", replace

keep if wage==1

bysort country: egen n_wage=count(estimate) if wage==1
label variable n_wage "# estimates (wage)"
keep country n_wage
duplicates drop country, force

	*Total
	preserve
	collapse (sum) n_wage
	sa "$data/tot.dta", replace
	restore
	append using "$data/tot.dta"
	
	
	*Rest of Europe
	preserve
	keep if country=="Austria" | country=="France" | country=="Germany" | country=="Greece" ///
	| country=="Ireland" | country=="Italy" | country=="Netherlands" | country=="Portugal" | country=="Spain" ///
	| country=="Switzerland" | country=="United Kingdom" | country=="Western Europe" | country=="European Economic Area" | country=="Albania" ///
	| country == "Norway" | country == "Sweden" |country == "Denmark" |country == "Finland"
	collapse (sum) n_wage
	gen country="Rest of Europe"
	sa "$data/resteu.dta", replace
	restore
	append using "$data/resteu.dta"
	

	*North America
	preserve
	keep if country=="Canada" | country=="United States" | country=="United States, Canada" 
	collapse (sum) n_wage
	gen country="North America"
	sa "$data/northamerica.dta", replace
	restore
	append using "$data/northamerica.dta"
	
	*Rest of the world
	preserve
	keep if country=="OECD countries" | country=="Australia" | country=="Israel" | country=="Turkey" 
	collapse (sum) n_wage
	gen country="Rest of the world"
	sa "$data/world.dta", replace
	restore
	append using "$data/world.dta"
	
save "${data}/n_e_wage.dta", replace


**********************************
*Number of estimates (employment)*
**********************************
clear all
set more off


use "${data}\database_clean.dta", replace

keep if empl==1


bysort country: egen n_empl=count(estimate) if empl==1
label variable n_empl "# estimates (employment)"
keep country n_empl
duplicates drop country, force

	*Total
	preserve
	collapse (sum) n_empl
	sa "$data/tot.dta", replace
	restore
	append using "$data/tot.dta"
	

	
	*Rest of Europe
	preserve
	keep if country=="Austria" | country=="France" | country=="Germany" | country=="Greece" ///
	| country=="Ireland" | country=="Italy" | country=="Netherlands" | country=="Portugal" | country=="Spain" ///
	| country=="Switzerland" | country=="United Kingdom" | country=="Western Europe" | country=="European Economic Area" | country=="Albania" ///
	| country == "Norway" | country == "Sweden" |country == "Denmark" |country == "Finland"
	collapse (sum) n_empl
	gen country="Rest of Europe"
	sa "$data/resteu.dta", replace
	restore
	append using "$data/resteu.dta"

	
	*North America
	preserve
	keep if country=="Canada" | country=="United States" | country=="United States, Canada" 
	collapse (sum) n_empl
	gen country="North America"
	sa "$data/northamerica.dta", replace
	restore
	append using "$data/northamerica.dta"
	
	*Rest of the world
	preserve
	keep if country=="OECD countries" | country=="Australia" | country=="Israel" | country=="Turkey" 
	collapse (sum) n_empl
	gen country="Rest of the world"
	sa "$data/world.dta", replace
	restore
	append using "$data/world.dta"

save "${data}/n_e_empl.dta", replace


*****************************************
*Institutions by countries (mean sd, n) *
*****************************************	
clear all
set more off


use "${data}\OECD_panel_countries_used.dta", replace
	

*Total	
preserve
collapse (mean) StrictEmpInd StrictEmpColl StrictEmpTemp CollBarg MinToMeanWage AverageTenure ALMPExp Unemployment GDPgrowth StateControl PMR NetReplacementRate index ///
(sd) sd_StrictEmpInd=StrictEmpInd sd_StrictEmpColl=StrictEmpColl sd_StrictEmpTemp=StrictEmpTemp sd_CollBarg=CollBarg sd_MinToMeanWage=MinToMeanWage sd_AverageTenure=AverageTenure sd_index=index ///
sd_ALMPExp=ALMPExp sd_Unemployment=Unemployment sd_GDPgrowth=GDPgrowth sd_StateControl=StateControl sd_PMR=PMR sd_NetReplacementRate=NetReplacementRate ///
(count) n_StrictEmpInd=StrictEmpInd n_StrictEmpColl=StrictEmpColl n_StrictEmpTemp=StrictEmpTemp n_CollBarg=CollBarg n_MinToMeanWage=MinToMeanWage n_AverageTenure=AverageTenure n_index=index ///
n_ALMPExp=ALMPExp n_Unemployment=Unemployment n_GDPgrowth=GDPgrowth n_StateControl=StateControl n_PMR=PMR n_NetReplacementRate=NetReplacementRate
sa "$data/tot.dta", replace
restore


*Rest of Europe
preserve
keep if country=="Austria" | country=="France" | country=="Germany" | country=="Greece" ///
	| country=="Ireland" | country=="Italy" | country=="Netherlands" | country=="Portugal" | country=="Spain" ///
	| country=="Switzerland" | country=="United Kingdom" | country=="Western Europe" | country=="European Economic Area" | country=="Albania" ///
	| country == "Norway" | country == "Sweden" |country == "Denmark" |country == "Finland"

collapse (mean) StrictEmpInd StrictEmpColl StrictEmpTemp CollBarg MinToMeanWage AverageTenure ALMPExp Unemployment GDPgrowth StateControl PMR NetReplacementRate index ///
(sd) sd_StrictEmpInd=StrictEmpInd sd_StrictEmpColl=StrictEmpColl sd_StrictEmpTemp=StrictEmpTemp sd_CollBarg=CollBarg sd_MinToMeanWage=MinToMeanWage sd_AverageTenure=AverageTenure sd_index=index ///
sd_ALMPExp=ALMPExp sd_Unemployment=Unemployment sd_GDPgrowth=GDPgrowth sd_StateControl=StateControl sd_PMR=PMR sd_NetReplacementRate=NetReplacementRate ///
(count) n_StrictEmpInd=StrictEmpInd n_StrictEmpColl=StrictEmpColl n_StrictEmpTemp=StrictEmpTemp n_CollBarg=CollBarg n_MinToMeanWage=MinToMeanWage n_AverageTenure=AverageTenure n_index=index ///
n_ALMPExp=ALMPExp n_Unemployment=Unemployment n_GDPgrowth=GDPgrowth n_StateControl=StateControl n_PMR=PMR n_NetReplacementRate=NetReplacementRate
gen country="Rest of Europe"
sa "$data/resteu.dta", replace
restore


*North America
preserve
keep if country=="Canada" | country=="United States" | country=="United States, Canada" 
collapse (mean) StrictEmpInd StrictEmpColl StrictEmpTemp CollBarg MinToMeanWage AverageTenure ALMPExp Unemployment GDPgrowth StateControl PMR NetReplacementRate index ///
(sd) sd_StrictEmpInd=StrictEmpInd sd_StrictEmpColl=StrictEmpColl sd_StrictEmpTemp=StrictEmpTemp sd_CollBarg=CollBarg sd_MinToMeanWage=MinToMeanWage sd_AverageTenure=AverageTenure sd_index=index ///
sd_ALMPExp=ALMPExp sd_Unemployment=Unemployment sd_GDPgrowth=GDPgrowth sd_StateControl=StateControl sd_PMR=PMR sd_NetReplacementRate=NetReplacementRate ///
(count) n_StrictEmpInd=StrictEmpInd n_StrictEmpColl=StrictEmpColl n_StrictEmpTemp=StrictEmpTemp n_CollBarg=CollBarg n_MinToMeanWage=MinToMeanWage n_AverageTenure=AverageTenure n_index=index ///
n_ALMPExp=ALMPExp n_Unemployment=Unemployment n_GDPgrowth=GDPgrowth n_StateControl=StateControl n_PMR=PMR n_NetReplacementRate=NetReplacementRate
gen country="North America"
sa "$data/northamerica.dta", replace
restore

*Rest of the world
preserve
keep if country=="OECD countries" | country=="Australia" | country=="Israel" | country=="Turkey" 
collapse (mean) StrictEmpInd StrictEmpColl StrictEmpTemp CollBarg MinToMeanWage AverageTenure ALMPExp Unemployment GDPgrowth StateControl PMR NetReplacementRate index ///
(sd) sd_StrictEmpInd=StrictEmpInd sd_StrictEmpColl=StrictEmpColl sd_StrictEmpTemp=StrictEmpTemp sd_CollBarg=CollBarg sd_MinToMeanWage=MinToMeanWage sd_AverageTenure=AverageTenure sd_index=index ///
sd_ALMPExp=ALMPExp sd_Unemployment=Unemployment sd_GDPgrowth=GDPgrowth sd_StateControl=StateControl sd_PMR=PMR sd_NetReplacementRate=NetReplacementRate ///
(count) n_StrictEmpInd=StrictEmpInd n_StrictEmpColl=StrictEmpColl n_StrictEmpTemp=StrictEmpTemp n_CollBarg=CollBarg n_MinToMeanWage=MinToMeanWage n_AverageTenure=AverageTenure n_index=index ///
n_ALMPExp=ALMPExp n_Unemployment=Unemployment n_GDPgrowth=GDPgrowth n_StateControl=StateControl n_PMR=PMR n_NetReplacementRate=NetReplacementRate
gen country="Rest of the world"
sa "$data/world.dta", replace
restore


*By country
collapse (mean) StrictEmpInd StrictEmpColl StrictEmpTemp CollBarg MinToMeanWage AverageTenure ALMPExp Unemployment GDPgrowth StateControl PMR NetReplacementRate index ///
(sd) sd_StrictEmpInd=StrictEmpInd sd_StrictEmpColl=StrictEmpColl sd_StrictEmpTemp=StrictEmpTemp sd_CollBarg=CollBarg sd_MinToMeanWage=MinToMeanWage sd_AverageTenure=AverageTenure sd_index=index ///
sd_ALMPExp=ALMPExp sd_Unemployment=Unemployment sd_GDPgrowth=GDPgrowth sd_StateControl=StateControl sd_PMR=PMR sd_NetReplacementRate=NetReplacementRate ///
(count) n_StrictEmpInd=StrictEmpInd n_StrictEmpColl=StrictEmpColl n_StrictEmpTemp=StrictEmpTemp n_CollBarg=CollBarg n_MinToMeanWage=MinToMeanWage n_AverageTenure=AverageTenure n_index=index ///
n_ALMPExp=ALMPExp n_Unemployment=Unemployment n_GDPgrowth=GDPgrowth n_StateControl=StateControl n_PMR=PMR n_NetReplacementRate=NetReplacementRate, by(country)

	append using "$data/tot.dta"
	append using "$data/resteu.dta"
	append using "$data/northamerica.dta"
	append using "$data/world.dta"

	
*Merge data
merge m:m country using "${data}/n_p_wage.dta"
drop _merge
sort country

merge m:m country using "${data}/n_e_empl.dta"
drop _merge
sort country

merge m:m country using "${data}/n_e_wage.dta"
drop _merge
sort country

merge m:m country using "${data}/n_p_empl.dta"
drop _merge
sort country

	replace country="Total" if country==""
	
	
	*Drop OECD and EEA (no wage estimates for these areas). If dropping "Western Europe" or "United States, Canada" remember to also drop them from totals
	drop if country=="OECD countries" | country=="European Economic Area"
	drop if country=="Western Europe" | country=="United States, Canada"

	
format n_wage n_p_wage n_empl n_p_empl n_StrictEmpInd n_StrictEmpColl n_StrictEmpTemp n_CollBarg n_MinToMeanWage n_AverageTenure n_ALMPExp n_Unemployment n_GDPgrowth n_StateControl n_PMR n_NetReplacementRate n_index %5.0fc
	
*Format variables
format StrictEmpInd sd_StrictEmpInd StrictEmpColl sd_StrictEmpColl StrictEmpTemp NetReplacementRate sd_StrictEmpTemp CollBarg sd_CollBarg MinToMeanWage sd_MinToMeanWage ///
AverageTenure sd_AverageTenure ALMPExp sd_ALMPExp StateControl sd_StateControl Unemployment sd_Unemployment GDPgrowth sd_GDPgrowth PMR sd_PMR sd_NetReplacementRate sd_index index %4.2f

*Convert to string variables
foreach var in n_wage n_p_wage n_empl n_p_empl StrictEmpInd StrictEmpColl StrictEmpTemp CollBarg MinToMeanWage AverageTenure ALMPExp Unemployment GDPgrowth StateControl PMR NetReplacementRate index ///
sd_StrictEmpInd sd_StrictEmpColl sd_StrictEmpTemp sd_CollBarg sd_MinToMeanWage sd_AverageTenure ALMPExp sd_ALMPExp StateControl sd_StateControl sd_PMR Unemployment sd_Unemployment GDPgrowth sd_GDPgrowth sd_NetReplacementRate sd_index ///
n_StrictEmpInd n_StrictEmpColl n_StrictEmpTemp n_CollBarg n_MinToMeanWage n_AverageTenure n_ALMPExp n_Unemployment n_GDPgrowth n_StateControl n_PMR n_NetReplacementRate n_index {
tostring `var', replace force usedisplayformat
}

*Replace missing with "-"
foreach var in n_wage n_p_wage n_empl n_p_empl StrictEmpInd StrictEmpColl StrictEmpTemp CollBarg MinToMeanWage AverageTenure ALMPExp Unemployment GDPgrowth StateControl index sd_index n_index sd_StrictEmpInd sd_StrictEmpColl sd_StrictEmpTemp sd_CollBarg sd_MinToMeanWage ///
sd_AverageTenure ALMPExp sd_ALMPExp StateControl sd_StateControl PMR sd_PMR NetReplacementRate sd_NetReplacementRate Unemployment sd_Unemployment GDPgrowth sd_GDPgrowth ///
n_StrictEmpInd n_CollBarg n_MinToMeanWage n_AverageTenure n_ALMPExp n_Unemployment n_GDPgrowth n_StateControl n_PMR n_NetReplacementRate {
replace `var'="-" if `var'=="."
}

*Replace N=0 with "-"
foreach var in n_StrictEmpInd n_StrictEmpColl n_StrictEmpTemp n_CollBarg n_MinToMeanWage n_AverageTenure n_ALMPExp n_Unemployment n_GDPgrowth n_StateControl n_PMR n_NetReplacementRate n_index  {
replace `var'="-" if `var'=="0"
}



*Sort data by region and alphabetically
gen sortvar=0 if country=="Total"
replace sortvar=1 if country == "Norway" | country == "Sweden" |country == "Denmark" |country == "Finland" | country=="Nordic countries"
replace sortvar=2 if country=="Albania" | country=="Austria" | country=="France" | country=="Germany" | country=="Greece" ///
	| country=="Ireland" | country=="Italy" | country=="Netherlands" | country=="Portugal" | country=="Spain" ///
	| country=="Switzerland" | country=="United Kingdom" | country=="Western Europe" | country=="European Economic Area" | country=="Rest of Europe" ///
	| country == "Norway" | country == "Sweden" |country == "Denmark" |country == "Finland"
replace sortvar=3 if country=="Canada" | country=="United States" | country=="United States, Canada" | country=="North America"
replace sortvar=4 if country=="OECD countries" | country=="Australia" | country=="Israel" | country=="Turkey" | country=="Rest of the world"

gen sortvar2=1 if country=="Total"
replace sortvar2=2 if country=="Nordic countries"
replace sortvar2=3 if country=="Rest of Europe"
replace sortvar2=4 if country=="North America"
replace sortvar2=5 if country=="Rest of the world"

sort sortvar sortvar2  country



*Drop totals
drop if country=="Nordic countries" | country=="Rest of Europe" | country=="North America" | country=="Rest of the world"

replace country="Mean" if country=="Total"
 
duplicates drop country, force

sort country
*Output table to latex
listtex country  StrictEmpInd StrictEmpColl AverageTenure CollBarg NetReplacementRate index if sortvar==2  ///
using "${out}/table2.tex", ///
rstyle(tabular) replace ///
head(\begin{tabular}{l*{5}cc} \toprule &  \multicolumn{1}{c}{(1)} & \multicolumn{1}{c}{(2)} & \multicolumn{1}{c}{(3)}  & \multicolumn{1}{c}{(4)} & \multicolumn{1}{c}{(5)} & \multicolumn{1}{c}{(6)} \\ &  \multicolumn{1}{c}{\emph{EPL (Regular)}} & \multicolumn{1}{c}{\emph{EPL (Collective)}} & \multicolumn{1}{c}{\emph{Average Job Tenure}} & \multicolumn{1}{c}{\emph{Collective Bargaining}} & \multicolumn{1}{c}{\emph{Net Replacement Rate}} & \multicolumn{1}{c}{\emph{Index}} \\  \midrule 	& \multicolumn{6}{c}{\emph{Europe}} \\) foot("")



*north america
listtex country  StrictEmpInd StrictEmpColl AverageTenure CollBarg NetReplacementRate index if sortvar==3  ///
using "${out}/table2_northamerica.tex", ///
rstyle(tabular) replace ///
head("") ///
foot("")

*rest of world
listtex country StrictEmpInd StrictEmpColl AverageTenure CollBarg NetReplacementRate index if sortvar==4  ///
using "${out}/table2_rest.tex", ///
rstyle(tabular) replace ///
head("") ///
foot("")

*total
listtex country StrictEmpInd StrictEmpColl AverageTenure CollBarg NetReplacementRate index if sortvar==0  ///
using "${out}/table2_total.tex", ///
rstyle(tabular) replace ///
head("") ///
foot("")


*standard error of institutional variables across countries and time
clear all
set more off

use "${data}\database_clean.dta", replace

keep StrictEmpInd StrictEmpColl AverageTenure CollBarg NetReplacementRate index country
duplicates drop

collapse (sd) StrictEmpInd StrictEmpColl AverageTenure CollBarg NetReplacementRate index
gen country="SD"

foreach var in  StrictEmpInd StrictEmpColl AverageTenure CollBarg NetReplacementRate index {
gen `var'_n= string(`var', "%3.2f")
drop `var'
rename `var'_n `var'
}



listtex country StrictEmpInd StrictEmpColl AverageTenure CollBarg NetReplacementRate index ///
using "${out}/table2_se.tex", ///
rstyle(tabular) replace ///
head("") ///
foot("\bottomrule \end{tabular}")



*Delete temporary datasets
erase "${data}\n_p_wage.dta"
erase "${data}\n_p_empl.dta"
erase "${data}\n_e_wage.dta"
erase "${data}\n_e_empl.dta"
erase "${data}\tot.dta"
erase "$data\resteu.dta"
erase "$data\northamerica.dta"
erase "$data\world.dta"
erase "$data\n_countries.dta"





